﻿/*
 * 1. 完成数据库的基本操作：连接（封装在内部）、查询（返回数据）、执行（不返回数据） 
 * 2. 用户使用时，应设置连接的基本信息（该类是一个Singleton类，只需要设置一次即可）
 */
using System;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;

namespace HousingDistribution
{
    class DBProc
    {
        public static DBProc Instance()
        {
            if (instance == null)
                instance = new DBProc();

            return instance;
        }

        public void SetServerInfo(string server, string db, int port, string uid, string pwd)
        {
            connStrBuilder.Clear();
            connStrBuilder.Append("server=");
            connStrBuilder.Append(server);
            connStrBuilder.Append(";");
            connStrBuilder.Append("database=");
            connStrBuilder.Append(db);
            connStrBuilder.Append(";");
            connStrBuilder.Append("user=");
            connStrBuilder.Append(uid);
            connStrBuilder.Append(";");
            connStrBuilder.Append("password=");
            connStrBuilder.Append(pwd);
            connStrBuilder.Append(";");
            connStrBuilder.Append("port=");
            connStrBuilder.Append(port);

            connStr = connStrBuilder.ToString();
        }

        //读取数据
        public DataSet ReadData(string cmdStr)
        {
            if (connStr == null)
                return null;

            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand cmd = new MySqlCommand("set names " + System.Text.Encoding.Default.HeaderName);
            MySqlDataAdapter myAdapter = new MySqlDataAdapter();

            
            cmd.CommandText = cmdStr;
            cmd.Connection = conn;

            DataSet myData = new DataSet();

            myAdapter.SelectCommand = cmd;
            myAdapter.Fill(myData);

            conn.Close();
            conn = null;
            cmd = null;
            myAdapter = null;

            return myData;
        }

        //执行 插入、删除等操作，不需要返回数据
        public void Excute(string cmdStr)
        {
            if (connStr == null)
                return;

            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand cmd = new MySqlCommand("set names " + System.Text.Encoding.Default.HeaderName);

            conn.Open();

            cmd.CommandText = cmdStr;
            cmd.Connection = conn;

            cmd.ExecuteNonQuery();

            conn.Close();
            conn = null;
            cmd = null;
        }

        //执行 插入操作，返回id值
        public int ExcuteInsertSql(string cmdStr)
        {
            if (connStr == null)
                return 0;

            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand cmd = new MySqlCommand("set names " + System.Text.Encoding.Default.HeaderName);

            conn.Open();

            cmd.CommandText = cmdStr;
            cmd.Connection = conn;

            cmd.ExecuteNonQuery();

            cmd.CommandText = "select last_insert_id()";

            MySqlDataAdapter myAdapter = new MySqlDataAdapter();
            myAdapter.SelectCommand = cmd;
            
            DataSet myData = new DataSet();
            myAdapter.Fill(myData);

            int id = Convert.ToInt32(myData.Tables[0].Rows[0][0]);

            conn.Close();
            conn = null;
            cmd = null;
            myAdapter = null;
            myData = null;

            return id;
        }

        private DBProc()
        {
        }

        private static DBProc instance = null;
        private StringBuilder connStrBuilder = new StringBuilder(128);
        private string connStr = null;  
    }
}
